package com.fs.base.utils;

import java.util.HashMap;
import java.util.Map;

/**
 * @author TiddlerCJ
 * @desc sql语句构造器 
 * @since v1.0
 * @date 2014-12-18
 */
public class SqlBuilder{
	/*
	 * 表名
	 */
	private String tableName;
	/*
	 * 表对应别名
	 */
	private String alias;
	/*
	 * 条件
	 */
	public StringBuffer where = new StringBuffer();
	/*
	 * 左连接
	 */
	private StringBuffer leftJoinOn;
	
	/*
	 * group by分组
	 */
	private String groupBy;
	
	
	private Map<String, String> orderBy = new HashMap<String, String>();
	/*
	 * select 语句中添加所需查询字段集合
	 */
	private Map<String, String> columnMap;
	
	/*
	 * 默认查询Column列表
	 */
	private String[] columns;
	
	/**
	 * 静态无参构造
	 */
	public static SqlBuilder build(){
		return new SqlBuilder();
	}
	
	/**
	 * @desc 静态有参构造
	 * @param tableName 表名
	 * @return
	 */
	public static SqlBuilder build(String tableName ){
		return new SqlBuilder(tableName);
	}
	
	/**
	 * @desc 静态有参构造
	 * @param tableName 表名
	 * @param alias 别名
	 * @return
	 */
	public static SqlBuilder build(String tableName,String alias){
		return new SqlBuilder(tableName,alias);
	}
	
	/**
	 *空构造函数
	 */
	private SqlBuilder() {
	} 
	
	/**
	 * @desc 有参构造
	 * @param tableName 表名
	 */
	private SqlBuilder(String tableName) {
		this.tableName = tableName;
	} 
	/**
	 * @desc 有参构造
	 * @param tableName 表名
	 * @param alias 别名
	 */
	private SqlBuilder(String tableName,String alias) {
		this.tableName = tableName;
		this.alias = alias;
	} 
	/**
	 * @desc 表名指定及别名
	 * @param tableName
	 * @param alias
	 * @return
	 */
	public SqlBuilder table(String tableName,String...alias){
		this.tableName = tableName;
		if(alias!=null && alias.length>0){
			this.alias = alias[0];
		}
		return this;
	}
	/**
	 * @desc 单独指定别名
	 * @param alias
	 * @return
	 */
	public SqlBuilder alias(String alias){
		this.alias = alias;
		return this;
	}
	
	/**
	 * 指定查询字段
	 * @param columns
	 * @return
	 */
	public SqlBuilder columns(String... columns){
		this.columns = columns;
		return this;
	}
	
	/**
	 * @desc like查询
	 * @param fieldName 字段名称
	 * @param fieldValue 字段值
	 * @return
	 */
	public SqlBuilder like(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("like fieldName Must not be null !");
		}
		where.append(" and ").append(fieldName).append(" like '%").append(fieldValue.toString()).append("%' ");
		return this;
	}
	
	
	public SqlBuilder orLike(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("orLike fieldName Must not be null !");
		}
		where.append(" or ").append(fieldName).append(" like '%").append(fieldValue.toString()).append("%' ");
		return this;
	}

	public SqlBuilder appendSql(String sql){
		if(!isBlank(sql))  where.append(" and ").append(sql);
		return this;
	}
	
	/**
	 * @desc like查询
	 * @param likeMap
	 * @param alias 别名
	 * @return
	 */
	public SqlBuilder like(Map<String, Object> likeMap,String...alias ){
		for(Object queryField :likeMap.keySet()){
			Object queryValue = likeMap.get(queryField);
			if(!isBlank(queryValue)){
				if(alias!=null && alias.length>0){
					where.append(" and ").append(alias[0]).append(".").append(queryField).append(" like '%").append(queryValue.toString()).append("%' ");
				}else{
					where.append(" and ").append(queryField).append(" like '%").append(queryValue.toString()).append("%' ");
				}
			}
		}
		return this;
	}
	
	/**
	 * @desc 等于查询
	 * @param fieldName 字段名称
	 * @param fieldValue 字段值
	 * @return
	 */
	public SqlBuilder eq(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("eq fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" and ").append(fieldName).append(" = '").append(String.valueOf(fieldValue).trim()).append("' ");
		}else{
			where.append(" and ").append(fieldName).append(" = ").append(String.valueOf(fieldValue).trim()).append(" ");
		}
		return this;
	}
	
	public SqlBuilder eqOr(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("eqOr fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" or ").append(fieldName).append(" = '").append(String.valueOf(fieldValue).trim()).append("' ");
		}else{
			where.append(" or ").append(fieldName).append(" = ").append(String.valueOf(fieldValue).trim()).append(" ");
		}
		return this;
	}
	
	/**
	 * @desc 不等于查询
	 * @param fieldName 字段名称
	 * @param fieldValue 字段值
	 * @return
	 */
	public SqlBuilder neq(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("neq fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" and ").append(fieldName).append(" <> '").append(String.valueOf(fieldValue).trim()).append("' ");
		}else{
			where.append(" and ").append(fieldName).append(" <> ").append(String.valueOf(fieldValue).trim()).append(" ");
		}
		return this;
	}
	
	/**
	 * @desc in查询
	 * @param fieldName
	 * @param fieldValue
	 * @return
	 */
	public SqlBuilder in(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("in fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" and ").append(fieldName).append(" in (").append(String.valueOf(fieldValue).trim()).append(") ");
		}else{
			where.append(" and ").append(fieldName).append(" in (").append(String.valueOf(fieldValue).trim()).append(") ");
		}
		return this;
	}
	
	/**
	 * @desc exists查询
	 * @param fieldName
	 * @param fieldValue
	 * @return
	 */
	public SqlBuilder exists(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("exists fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" and ").append(fieldName).append(" exists (").append(String.valueOf(fieldValue).trim()).append(") ");
		}else{
			where.append(" and ").append(fieldName).append(" exists (").append(String.valueOf(fieldValue).trim()).append(") ");
		}
		return this;
	}

	/**
	 * @desc not exists查询
	 * @param fieldName
	 * @param fieldValue
	 * @return
	 */
	public SqlBuilder nExists(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("nExists fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" and ").append(fieldName).append(" not exists (").append(String.valueOf(fieldValue).trim()).append(") ");
		}else{
			where.append(" and ").append(fieldName).append(" not exists (").append(String.valueOf(fieldValue).trim()).append(") ");
		}
		return this;
	}
	
	/**
	 * @desc not in查询
	 * @param fieldName
	 * @param fieldValue
	 * @return
	 */
	public SqlBuilder nIn(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("nIn fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" and ").append(fieldName).append(" not  in (").append(String.valueOf(fieldValue).trim()).append(") ");
		}else{
			where.append(" and ").append(fieldName).append(" not  in (").append(String.valueOf(fieldValue).trim()).append(") ");
		}
		return this;
	}
	
	/**
	 * @desc 大于等于查询
	 * @param fieldName 字段名称
	 * @param fieldValue 字段值
	 * @return
	 */
	public SqlBuilder ge(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("ge fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" and ").append(fieldName).append(" >= '").append(fieldValue.toString()).append("' ");
		}else{
			where.append(" and ").append(fieldName).append(" >= ").append(fieldValue.toString()).append(" ");
		}
		return this;
	}
	
	/**
	 * @desc 大于查询
	 * @param fieldName 字段名称
	 * @param fieldValue 字段值
	 * @return
	 */
	public SqlBuilder gt(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("gt fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" and ").append(fieldName).append(" > '").append(fieldValue.toString()).append("' ");
		}else{
			where.append(" and ").append(fieldName).append(" > ").append(fieldValue.toString()).append(" ");
		}
		return this;
	}
	
	/**
	 * @desc 小于等于查询
	 * @param fieldName 字段名称
	 * @param fieldValue 字段值
	 * @return
	 */
	public SqlBuilder le(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("le fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" and ").append(fieldName).append(" <= '").append(fieldValue.toString()).append("' ");
		}else{
			where.append(" and ").append(fieldName).append(" <= ").append(fieldValue.toString()).append(" ");
		}
		return this;
	}
	
	/**
	 * @desc 小于查询
	 * @param fieldName 字段名称
	 * @param fieldValue 字段值
	 * @return
	 */
	public SqlBuilder lt(String fieldName,Object fieldValue){
		if(isBlank(fieldName)){
			 throw new IllegalArgumentException("lt fieldName Must not be null !");
		}
		if("class java.lang.String".equals(fieldValue.getClass().toString())){
			where.append(" and ").append(fieldName).append(" < '").append(fieldValue.toString()).append("' ");
		}else{
			where.append(" and ").append(fieldName).append(" < ").append(fieldValue.toString()).append(" ");
		}
		return this;
	}
	
	/**
	 * @desc 添加查询结果字段
	 * @param fieldName 字段名称
	 * @param fieldValue 字段值
	 * @return
	 */
	public SqlBuilder addColumn(String column,String...alias){
		if(isBlank(column)){
			 throw new IllegalArgumentException("addColumn column Must not be null !");
		}
		if(columnMap==null){
			columnMap = new HashMap<String, String>();
		}
		if(alias!=null && alias.length>0){
			this.columnMap.put(column, alias[0]);
		}else{
			this.columnMap.put(column, "");
		}
		return this;
	}
	
	/**
	 * @desc 左连接查询
	 * @param tableName 左连接表名
	 * @param on 连接条件
	 * @param alias 指定表别名
	 * @return
	 */
	public SqlBuilder leftJoinOn(String tableName,String on,String...alias){
		if(leftJoinOn==null){
			leftJoinOn = new StringBuffer();
		}
		if(isBlank(tableName)){
			 throw new IllegalArgumentException("leftJoinOn tableName Must not be null !");
		}
		leftJoinOn.append(" left join ").append(tableName);
		if(alias!=null && alias.length>0){
			leftJoinOn.append(" as ").append(alias[0]);
		}
		leftJoinOn.append(" on ").append(on).append(" ");
		return this;
	}
	
	/**
	 * @desc 排序
	 * @param column 排序字段
	 * @param order 升序或是降序 ，默认asc 排序
	 * @return
	 */
	public SqlBuilder orderBy(String column,String...order){
		if(!isBlank(column)){
			orderBy.put("key", column);
			if(order!=null && order.length>0){
				String orderValue = order[0];
				if(orderValue.equals("desc") || orderValue.equals("asc") ){
					orderBy.put("value", orderValue);
				}
			}else{
				orderBy.put("value", " asc ");
			}
		}
		return this;
	}
	
	/**
	 * @desc 分组
	 * @param column
	 * @return
	 */
	public SqlBuilder groupBy(String groupBy){
		this.groupBy = groupBy;
		return this;
	}
	
	/**
	 * @desc 删除sql
	 * @return
	 */
	public String delete(){
		if(isBlank(tableName)){
			 throw new IllegalArgumentException("tableName Must not be null !");
		}
		if(isBlank(where.toString())){
			throw new IllegalArgumentException("where Must not be null !");
		}
		StringBuffer sql = new StringBuffer("delete from ");
		sql.append(tableName).append(" where 1=1 ");
		sql.append(where);
		
		return sql.toString();
	}
	
	/**
	 * @desc select 执行
	 * @return
	 */
	public String select(){
		StringBuffer sql = new StringBuffer("select");
		if(isBlank(tableName)){
			 throw new IllegalArgumentException("tableName Must not be null !");
		}
		boolean isFirstColumns = true;
		boolean  aliasFlag = isBlank(alias);
		if(columns!=null){
			for(String column: columns){
				if(isFirstColumns==false) sql.append(",");
				if(aliasFlag){
					sql.append(" ").append(column);
				}else{
					sql.append(" ").append(alias).append(".").append(column);
				}
				if(isFirstColumns==true){ isFirstColumns = false; }
			}
		}else{
			if(!aliasFlag){
				sql.append(" ").append(alias).append(".* ");
			}else{
				sql.append(" * ");
			}
		}
		
		if(columnMap!=null){
			for(Object columnName :columnMap.keySet()){
				String column_alias = columnMap.get(columnName);
				sql.append(",");
				sql.append(columnName);
				if(column_alias!=null && !"".equals(column_alias)){
					sql.append(" as ").append(column_alias);
				}
			}
		}
		sql.append(" from ").append(tableName).append(" ");
		
		if(!isBlank(alias)){
			sql.append(" as ").append(alias);
		}
		
		if(leftJoinOn!=null){
			sql.append(leftJoinOn);
		}
		
		sql.append(" where 1=1  ");
		
		sql.append(where);
		
		if(!isBlank(groupBy)){
			sql.append(" group by ").append(groupBy);
		}
		
		if(orderBy.containsKey("key")){
			sql.append(" order by ").append(orderBy.get("key"));
		}
		if(orderBy.containsKey("value")){
			sql.append(" ").append(orderBy.get("value"));
		}
		return sql.toString();
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getAlias() {
		return alias;
	}

	public void setAlias(String alias) {
		this.alias = alias;
	}

	public StringBuffer getBuild() {
		return where;
	}

	public void setBuild(StringBuffer build) {
		this.where = build;
	}

	public StringBuffer getLeftJoinOn() {
		return leftJoinOn;
	}

	public void setLeftJoinOn(StringBuffer leftJoinOn) {
		this.leftJoinOn = leftJoinOn;
	}

	public StringBuffer getWhere() {
		return where;
	}

	public void setWhere(StringBuffer where) {
		this.where = where;
	}

	public Map<String, String> getColumnMap() {
		return columnMap;
	}

	public void setColumnMap(Map<String, String> columnMap) {
		this.columnMap = columnMap;
	}
	
	private boolean isBlank(Object str){
		return str==null || "".equals(str) ;
	}
	
	public String toString() {
		return "SqlBuilder [tableName:" + tableName +",alias:"+alias +",where:("+where + "),leftJoinOn:"+leftJoinOn +",groupBy:"+groupBy + ",orderBy:"+orderBy +",columnMap:"+columnMap +" column:"+ columns + "]";
	}
	
	/**
	public static void main(String[] args) {
		 * 1.单表全表查询
		String sql = SqlBuilder.build("user").select();
		System.out.println(sql);
		 */
		
		/**
		 * 2.单表采用别名查询
		String sql = SqlBuilder.build("user","u").select();
		System.out.println(sql);
		 */
		
		/**
		 * 3.单表部分字段查询
		String sql = SqlBuilder.build("user","u").columns("age","name").select();
		System.out.println(sql);
		 */
		
		/**
		 * 4.
		String sql = SqlBuilder.build("user","u").columns("age","name").gt("age", 10).select();
		System.out.println(sql);
		 */
		
		/**
		 * 5.添加自定义查询字段及别名
		String sql = SqlBuilder.build("user","u").addColumn("u.age", "uAge").select();
		System.out.println(sql);
		 */
		
		/**
		 * 6.复杂条件查询 
		String sql = SqlBuilder.build("user","u").le("age", 30).like("name", "张").select();
		System.out.println(sql);
		 */
		
		/**
		 * 7.多表关联查询
		String sql = SqlBuilder.build("user","u")
				.leftJoinOn("sys_user_role", "u.id=ur.user_id", "ur")
				.leftJoinOn("sys_user_role", "u.id=sur.user_id", "sur").select();
		System.out.println(sql);
		 */
		
		/**
		 * 8.排序查询
		String sql = SqlBuilder.build("user","u").orderBy("age").select();
		System.out.println(sql);
		 */
		
		/**
		 * 9.拼接sql
		String sql = SqlBuilder.build("user","u").appendSql("u.age<100").orderBy("age").select();
		System.out.println(sql);
		 */
		
		/**
		 * 10.分组查询
		String sql = SqlBuilder.build("user","u").columns("age").groupBy("age").select();
		System.out.println(sql);
		 */
		
		/**
		 * 11.删除操作
		String sql = SqlBuilder.build("user","u").le("age", 5).delete();
		System.out.println(sql);
		 
	}*/

}
